Quick Formulas

Quick Formulas allow users to quickly build mathematical and statistical logic on the selected items in a single step. The formula options exposed in the 'quick' menus are directed at the typical listing of functions users often need, and are driven by the type of members selected (dates, measures, normal hierarchy elements etc) and the number of elements selected (one, two or more). All quick formulas can be fully customized and edited using the Formulate tools - which allow the user to construct ANY type of mathematical operation possible within the given data model and language (PQL for SQL data sources and MDX for others).

Note: All quick formulas built directly in Discover are initially built as one-of, report specific items. However, they can be promoted a sharable item. See here for more.

Types of Quick Formulas

The heuristic engine in Pyramid changes the types of quick formulas available based on what is selected. The selections can be made from the element trees, or directly from visuals.

Single Element

Math functions

These methods are classic mathematical operations for any element on both hierarchies and measures: for example, Absolute value:

Date time functions

These methods are designed to produce date time logic on elements from date-time hierarchies only. They will only work on non-year elements: for example, Year to date.

Note: The type of date functions exposed changes based on the hierarchy type. Therefore, the options for quarters are different to those for months or actual dates.

Note: Two extra functions are available for MDX data sources (like MS OLAP, Tabular and SAP BW): Parallel Period and Periods to Date

Hierarchical Function

These methods are designed to produce hierarchical logic on element from regular, multilevel hierarchies only. For example Percentage of Parent.

Dual Elements

Constructed Functions

These methods offer basic math operations using the two selected elements or measures. This includes Addition, Percentage Variance and Ratio.

Note: The custom measure calculation will be applied according to the order in which the items are selected. For example, to subtract Returns from Quantity, be sure to select Quantity first, and then Returns.

Range Aggregate Functions

These methods offer statistical aggregation logic using the RANGE of elements BETWEEN the two selected items. This means that picking the "average" range aggregation using January and June will build a formula of the average across all the months between January and June inclusive.

Aggregate Functions

These methods offer statistical aggregation logic using the selected elements only. This means that picking the "average" aggregation using January and June will build a formula of the average across January and June only.

Notes: There are no special date-time formulas when using two or more date time elements.

Note: There are no aggregation function options for measures apart from "SUM".

Multiple Elements

Once more than two elements have been chosen, the only quick calc options available are "aggregate functions" - described above. Further, the only measure based function is a simple "Sum" operation.

Building Quick Formulas

To build quick calculations start by making selections of the relevant elements. Then right click, and choose the relevant calculation from the context menus. See examples and the below and the attached video for more.

• Click here to see a demo of how to build formulas.

Managing Quick Formulas

Formulas created from the quick menus can be fully edited, deleted and shared like any other formula item in the system. Click here to read more on how to manage quick formulas.

Examples